﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using MySql.Data;//para el datatable
using MySql.Data.MySqlClient;
using Mapeo;
using NHibernate;


namespace DAL
{
    public class CasosEspeciales_DAL : Controlador
    {
        public DataTable CasosEspeciales_ListaxSemestre(string semestre)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);
                    SqlCmd.CommandText = "sp_CasosEspeciales_xSemestre";
                    SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCmd.Parameters.AddWithValue("@in_semestre", semestre);

                    SqlCmd.ExecuteNonQuery();
                    MySqlDataAdapter da = new MySqlDataAdapter(SqlCmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    Trans.Commit();
                    return dt;
                }
            }

        }

        public DataTable CasosEspeciales_ListaxSemestre_xCodigo(string semestre,string codigo)
        {
            String cadena = "";
            Conexion conex = new Conexion();
            MySqlCommand cmd = new MySqlCommand();
            MySqlDataAdapter da = new MySqlDataAdapter();
            DataTable data = new DataTable();
            MySqlConnection conexion = new MySqlConnection();
            cadena = conex.ConexionCampusVirtual();
            conexion.ConnectionString = cadena;
            try
            {
                conexion.Open();
                cmd.CommandText = "sp_CasosEspeciales_xSemestre_xCodigo";
                cmd.Parameters.AddWithValue("@in_semestre", semestre);
                cmd.Parameters.AddWithValue("@in_codigo", codigo);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = conexion;

                da = new MySqlDataAdapter(cmd);
                da.Fill(data);

                return data;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                conexion.Close();
                cmd.Dispose();
            }
        }

        public DataTable CasosEspeciales_ListaxSemestre_xCodigo_xNombre(string semestre, string codigo,string nombre)
        {
            String cadena = "";
            Conexion conex = new Conexion();
            MySqlCommand cmd = new MySqlCommand();
            MySqlDataAdapter da = new MySqlDataAdapter();
            DataTable data = new DataTable();
            MySqlConnection conexion = new MySqlConnection();
            cadena = conex.ConexionCampusVirtual();
            conexion.ConnectionString = cadena;
            try
            {
                conexion.Open();
                cmd.CommandText = "sp_CasosEspeciales_xSemestre_xCodigo_xNombre";
                cmd.Parameters.AddWithValue("@in_semestre", semestre);
                cmd.Parameters.AddWithValue("@in_codigo", codigo);
                cmd.Parameters.AddWithValue("@in_nombre", nombre);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = conexion;

                da = new MySqlDataAdapter(cmd);
                da.Fill(data);

                return data;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                conexion.Close();
                cmd.Dispose();
            }
        }

        public DataTable CasosEspeciales_ListaxSemestre_xCodigo_xNombre_xEspec(string semestre, string codigo, string nombre, string espec)
        {
            String cadena = "";
            Conexion conex = new Conexion();
            MySqlCommand cmd = new MySqlCommand();
            MySqlDataAdapter da = new MySqlDataAdapter();
            DataTable data = new DataTable();
            MySqlConnection conexion = new MySqlConnection();
            cadena = conex.ConexionCampusVirtual();
            conexion.ConnectionString = cadena;
            try
            {
                conexion.Open();
                cmd.CommandText = "sp_CasosEspeciales_xSemestre_xCodigo_xNombre_xEspec";
                cmd.Parameters.AddWithValue("@in_semestre", semestre);
                cmd.Parameters.AddWithValue("@in_codigo", codigo);
                cmd.Parameters.AddWithValue("@in_nombre", nombre);
                cmd.Parameters.AddWithValue("@in_codespec", espec);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = conexion;

                da = new MySqlDataAdapter(cmd);
                da.Fill(data);

                return data;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                conexion.Close();
                cmd.Dispose();
            }
        }        

        public DataTable CasosEspeciales_ListaxSemestre_xCodigoNombre_xEspec(string semestre, string codigoNombre, string espec)
        {
            String cadena = "";
            Conexion conex = new Conexion();
            MySqlCommand cmd = new MySqlCommand();
            MySqlDataAdapter da = new MySqlDataAdapter();
            DataTable data = new DataTable();
            MySqlConnection conexion = new MySqlConnection();
            cadena = conex.ConexionCampusVirtual();
            conexion.ConnectionString = cadena;
            try
            {
                conexion.Open();
                cmd.CommandText = "sp_CasosEspeciales_xSemestre_xCodigoNombre_xEspec";
                cmd.Parameters.AddWithValue("@in_semestre", semestre);
                cmd.Parameters.AddWithValue("@in_codigonombre", codigoNombre);
                cmd.Parameters.AddWithValue("@in_espec", espec);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = conexion;
                cmd.CommandTimeout = 7200;

                da = new MySqlDataAdapter(cmd);
                da.Fill(data);

                return data;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                conexion.Close();
                cmd.Dispose();
            }
        }

        //public DataTable CasosEspeciales_ListaTipoCaso_xSemestre_xCodigo(string semestre, string codigo)
        //{
        //    String cadena = "";
        //    Conexion conex = new Conexion();
        //    MySqlCommand cmd = new MySqlCommand();
        //    MySqlDataAdapter da = new MySqlDataAdapter();
        //    DataTable data = new DataTable();
        //    MySqlConnection conexion = new MySqlConnection();
        //    cadena = conex.ConexionCampusVirtual();
        //    conexion.ConnectionString = cadena;
        //    try
        //    {
        //        conexion.Open();
        //        cmd.CommandText = "sp_CasosEspeciales_TipoCaso_xSemestre_xCodigo";
        //        cmd.Parameters.AddWithValue("@in_semestre", semestre);
        //        cmd.Parameters.AddWithValue("@in_codigo", codigo);
        //        cmd.CommandType = CommandType.StoredProcedure;
        //        cmd.Connection = conexion;

        //        da = new MySqlDataAdapter(cmd);
        //        da.Fill(data);

        //        return data;
        //    }
        //    catch (Exception)
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        conexion.Close();
        //        cmd.Dispose();
        //    }
        //}

        public DataTable CasosEspeciales_ultimo_ID_OBS()
        {
            String cadena = "";
            Conexion conex = new Conexion();
            MySqlCommand cmd = new MySqlCommand();
            MySqlDataAdapter da = new MySqlDataAdapter();
            DataTable data = new DataTable();
            MySqlConnection conexion = new MySqlConnection();
            cadena = conex.ConexionCampusVirtual();
            conexion.ConnectionString = cadena;

            try 
	        {	        
		        conexion.Open();
                string sql = "select MAX(ID_OBS) as ID_OBS from data_obs";
                cmd.Connection = conexion;
                cmd.CommandText = sql;
                da.SelectCommand = cmd;
                da.Fill(data);

                return data;
	        }
	        catch (Exception)
	        {
		
		        throw;
	        }
             finally
            {
                conexion.Close();
                cmd.Dispose();
            }                       
        }



        //Para que me liste 20141 y 20142
        public DataTable CasosEspeciales_ListaxSemestre_2Forma(string semestre)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);
                    SqlCmd.CommandText = "sp_CasosEspeciales_xSemestre_2Forma";
                    SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCmd.Parameters.AddWithValue("@in_semestre", semestre);

                    SqlCmd.ExecuteNonQuery();
                    MySqlDataAdapter da = new MySqlDataAdapter(SqlCmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    Trans.Commit();
                    return dt;
                }
            }
        }

        //Para Cursos 20141 y 20142
        //public DataTable CasosEspeciales_ListaTipoCaso_xSemestre_xCodigo_2Forma(string semestre, string codigo)
        //{
        //    using (ISession Sesion = m_SessionFactory.OpenSession())
        //    {
        //        using (ITransaction Trans = Sesion.BeginTransaction())
        //        {
        //            MySqlCommand SqlCmd = new MySqlCommand();
        //            SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
        //            Trans.Enlist(SqlCmd);
        //            SqlCmd.CommandText = "sp_CasosEspeciales_TipoCaso_xSemestre_xCodigo_2Forma";
        //            SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
        //            SqlCmd.Parameters.AddWithValue("@in_semestre", semestre);
        //            SqlCmd.Parameters.AddWithValue("@in_codigo", codigo);
        //            SqlCmd.ExecuteNonQuery();
        //            MySqlDataAdapter da = new MySqlDataAdapter(SqlCmd);
        //            DataTable dt = new DataTable();
        //            da.Fill(dt);
        //            Trans.Commit();
        //            return dt;
        //        }
        //    }
        //}



        public DataTable CasosEspeciales_Curso_Alumno(string semestre, string codigo)
        {
            using (ISession Sesion = m_SessionFactory.OpenSession())
            {
                using (ITransaction Trans = Sesion.BeginTransaction())
                {
                    MySqlCommand SqlCmd = new MySqlCommand();
                    SqlCmd.Connection = (MySqlConnection)Sesion.Connection;
                    Trans.Enlist(SqlCmd);
                    SqlCmd.CommandText = "sp_CasosEspeciales_Curso_Alumno";
                    SqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlCmd.Parameters.AddWithValue("@in_semestre", semestre);
                    SqlCmd.Parameters.AddWithValue("@in_codigo", codigo);
                    SqlCmd.ExecuteNonQuery();
                    MySqlDataAdapter da = new MySqlDataAdapter(SqlCmd);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    Trans.Commit();
                    return dt;
                }
            }
        }


    }
}
